package by.dezhits.makeanote.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import by.dezhits.makeanote.content.contract.FoldersContract;
import by.dezhits.makeanote.content.contract.NotesContract;

public class DBHelper extends SQLiteOpenHelper {

	public class DBFlags {
		public static final String TRUE = "TRUE";
		public static final String FALSE = "FALSE";
	}

	private static final int DATABASE_VERSION = 1;

	private static final String DATABASE_NAME = "makenote8.store.db";

	public static final String NOTES_TABLE = "NOTES";

	public static final String FOLDERS_TABLE = "FOLDERS";

	public static final String[] NOTES_COLUMNS = {
			NotesContract.NotesColumns.ID, NotesContract.NotesColumns.TITLE,
			NotesContract.NotesColumns.TEXT, NotesContract.NotesColumns.DATE,
			NotesContract.NotesColumns.FOLDER,
			NotesContract.NotesColumns.CLOSED };

	public static final String[] FOLDERS_COLUMNS = {
			FoldersContract.FoldersColumns.ID,
			FoldersContract.FoldersColumns.NAME,
			FoldersContract.FoldersColumns.PASS };

	public static final int NOTES_ID_COL_INDEX = 0;
	public static final int NOTES_TITLE_COL_INDEX = 1;
	public static final int NOTES_TEXT_COL_INDEX = 2;
	public static final int NOTES_DATE_COL_INDEX = 3;
	public static final int NOTES_FOLDER_COL_INDEX = 4;
	public static final int NOTES_CLOSED_COL_INDEX = 5;

	public static final int FOLDERS_ID_COL_INDEX = 0;
	public static final int FOLDERS_NAME_COL_INDEX = 1;
	public static final int FOLDERS_PASS_COL_INDEX = 2;

	public static final String CREATE_NOTES_TABLE_SQL = "CREATE TABLE "
			+ NOTES_TABLE + " (" + NOTES_COLUMNS[NOTES_ID_COL_INDEX]
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ NOTES_COLUMNS[NOTES_TITLE_COL_INDEX] + " VARCHAR NOT NULL, "
			+ NOTES_COLUMNS[NOTES_TEXT_COL_INDEX] + " VARCHAR, "
			+ NOTES_COLUMNS[NOTES_DATE_COL_INDEX] + " VARCHAR NOT NULL, "
			+ NOTES_COLUMNS[NOTES_FOLDER_COL_INDEX] + " VARCHAR, "
			+ NOTES_COLUMNS[NOTES_CLOSED_COL_INDEX] + " VARCHAR NOT NULL)";

	public static final String CREATE_FOLDERS_TABLE_SQL = "CREATE TABLE "
			+ FOLDERS_TABLE + " (" + FOLDERS_COLUMNS[FOLDERS_ID_COL_INDEX]
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ FOLDERS_COLUMNS[FOLDERS_NAME_COL_INDEX] + " VARCHAR NOT NULL, "
			+ FOLDERS_COLUMNS[FOLDERS_PASS_COL_INDEX] + " VARCHAR, "
			+ "UNIQUE (" + FOLDERS_COLUMNS[FOLDERS_NAME_COL_INDEX] + "))";

	public static final String DROP_NOTES_TABLE_SQL = "DROP TABLE IF EXISTS "
			+ NOTES_TABLE;

	public static final String DROP_FOLDERS_TABLE_SQL = "DROP TABLE IF EXISTS "
			+ FOLDERS_TABLE;

	public DBHelper(final Context context, final CursorFactory factory) {
		super(context, DATABASE_NAME, factory, DATABASE_VERSION);
	}

	@Override
	public final void onCreate(final SQLiteDatabase db) {
		try {
			db.beginTransaction();
			db.execSQL(CREATE_NOTES_TABLE_SQL);
			db.execSQL(CREATE_FOLDERS_TABLE_SQL);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}

	}

	@Override
	public final void onUpgrade(final SQLiteDatabase db, final int oldVersion,
			final int newVersion) {
		try {
			db.beginTransaction();
			db.execSQL(DROP_NOTES_TABLE_SQL);
			db.execSQL(DROP_FOLDERS_TABLE_SQL);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
		onCreate(db);
	}

	public Cursor getNotes(String selection, String[] selectionArgs,
			String sortOrder) {
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(DBHelper.NOTES_TABLE, null, selection,
				selectionArgs, null, null, sortOrder);
		return cursor;
	}

	public void deleteNotes(String selection) {
		SQLiteDatabase db = getReadableDatabase();
		db.delete(NOTES_TABLE, selection, null);
	}

	public void addNote(Integer id, String title, String text, String date,
			String folder, String closed) {
		SQLiteDatabase db = getWritableDatabase();
		ContentValues values = new ContentValues();
		if (id != null) {
			values.put(DBHelper.NOTES_COLUMNS[DBHelper.NOTES_ID_COL_INDEX], id);
		}
		values.put(DBHelper.NOTES_COLUMNS[DBHelper.NOTES_TITLE_COL_INDEX],
				title);
		values.put(DBHelper.NOTES_COLUMNS[DBHelper.NOTES_TEXT_COL_INDEX], text);
		values.put(DBHelper.NOTES_COLUMNS[DBHelper.NOTES_DATE_COL_INDEX], date);
		values.put(DBHelper.NOTES_COLUMNS[DBHelper.NOTES_FOLDER_COL_INDEX],
				folder);
		values.put(DBHelper.NOTES_COLUMNS[DBHelper.NOTES_CLOSED_COL_INDEX],
				closed);
		db.insertWithOnConflict(DBHelper.NOTES_TABLE, null, values,
				SQLiteDatabase.CONFLICT_REPLACE);
	}

	public Cursor getFolders(String selection, String[] selectionArgs,
			String sortOrder) {
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(DBHelper.FOLDERS_TABLE, null, selection,
				selectionArgs, null, null, sortOrder);
		return cursor;
	}

	public void deleteFolders(String selection) {
		SQLiteDatabase db = getReadableDatabase();
		db.delete(FOLDERS_TABLE, selection, null);
	}

	public long addFolder(String name, String pass) {
		SQLiteDatabase db = getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(DBHelper.FOLDERS_COLUMNS[DBHelper.FOLDERS_NAME_COL_INDEX],
				name);
		values.put(DBHelper.FOLDERS_COLUMNS[DBHelper.FOLDERS_PASS_COL_INDEX],
				pass);
		return db.insertWithOnConflict(DBHelper.FOLDERS_TABLE, null, values,
				SQLiteDatabase.CONFLICT_IGNORE);
	}

	public long setPasswordForFolder(Integer id, String name, String pass) {
		SQLiteDatabase db = getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(DBHelper.FOLDERS_COLUMNS[DBHelper.FOLDERS_ID_COL_INDEX], id);
		values.put(DBHelper.FOLDERS_COLUMNS[DBHelper.FOLDERS_NAME_COL_INDEX],
				name);
		values.put(DBHelper.FOLDERS_COLUMNS[DBHelper.FOLDERS_PASS_COL_INDEX],
				pass);
		return db.insertWithOnConflict(DBHelper.FOLDERS_TABLE, null, values,
				SQLiteDatabase.CONFLICT_REPLACE);
	}

}
